﻿CREATE PROCEDURE [dbo].[GetTotal] 
@type varchar(100)='',
@startDt varchar(50)='',
@endDt varchar(50)=''
AS
declare @type_old varchar(100)
declare @BaoGaoDanShu varchar(100),@YuYueShu varchar(100),@GuaHaoShu varchar(100),@JiaoFeiShu varchar(100),@BangKaShu varchar(100),@YongHuShu varchar(100),@YiShengShu varchar(100),@RiYongHuShu varchar(100),@JiaoYiShu varchar(100),@JiaoYiJinE money,@YuYueJiaoFeiShu float

declare @BaoGaoDanShuTot varchar(100),@YuYueShuTot varchar(100),@GuaHaoShuTot varchar(100),@JiaoFeiShuTot varchar(100),@BangKaShuTot varchar(100),@YongHuShuTot varchar(100),@YiShengShuTot varchar(100),@RiYongHuShuTot varchar(100),@JiaoYiShuTot varchar(100),@JiaoYiJinETot money,@YuYueJiaoFeiShuTot float

declare @YuYueJiaoYiShu int,@JiaoFeiJiaoYiShu int,@YuYueJiaoYiShuTot int,@JiaoFeiJiaoYiShuTot int

declare @YuYueJiaoYiJinE money,@JiaoFeiJiaoYiJinE money,@YuYueJiaoYiJinETot money,@JiaoFeiJiaoYiJinETot money

CREATE TABLE #linshibiao
(
[name] [varchar](100) null,
[value][varchar](100) null,
[TotalValue][varchar](100) null,
[type][int] null
)

if @type<>''
begin
set @type_old=','+@type+','
end
if CHARINDEX ( ',1,' , @type_old)>0 or @type=''
begin
	if @startDt<>'' and @endDt <> ''
		begin
			select @BaoGaoDanShu=COUNT(*) from EventLog where Controller='WeiXin.Web.Mvc.Controllers.				WxReportController' and [Action]='InspectionDetail' and DATEDIFF(N,AddTime,@startDt)<=0 and				DATEDIFF(N,AddTime,@endDt) >=0
			select @BaoGaoDanShuTot=COUNT(*) from EventLog where Controller='WeiXin.Web.Mvc.Controllers.			WxReportController' and [Action]='InspectionDetail' and DATEDIFF(N,AddTime,@endDt) >=0
			
		end	
	if @startDt='' and @endDt = ''
		begin
			select @BaoGaoDanShu=COUNT(*) from EventLog where Controller='WeiXin.Web.Mvc.Controllers.				WxReportController' and [Action]='InspectionDetail'
			select @BaoGaoDanShuTot=0
		end
		insert into #linshibiao values('BaoGaoDanShu',@BaoGaoDanShu,@BaoGaoDanShuTot,1)
end
if CHARINDEX ( ',2,' , @type_old)>0 or @type=''
begin
	if @startDt='' and @endDt = ''
		begin
			select @YuYueShu = COUNT(*) from ApptOrder where OrderType=0 and OrderStatus=21
			select @YuYueShuTot=0
		end
	if @startDt<>'' and @endDt <> ''
		begin
			select @YuYueShu = COUNT(*) from ApptOrder where OrderType=0 and OrderStatus=21 and DATEDIFF(N,			CreateTime,@startDt)<=0 and DATEDIFF(N,CreateTime,@endDt) >=0
			select @YuYueShuTot = COUNT(*) from ApptOrder where OrderType=0 and OrderStatus=21 and					DATEDIFF (N,CreateTime,@endDt) >=0
		end
	insert into #linshibiao values('YuYueShu',@YuYueShu,@YuYueShuTot,2)
end
if CHARINDEX ( ',3,' , @type_old)>0 or @type=''
begin
	if @startDt='' and @endDt = ''
		begin
			select @GuaHaoShu = COUNT(*) from ApptOrder where OrderType=2 and OrderStatus=21
			select @GuaHaoShuTot=0
		end
	if @startDt<>'' and @endDt <> ''
		begin
			select @GuaHaoShu = COUNT(*) from ApptOrder where OrderType=0 and OrderStatus=21 and DATEDIFF			(N,CreateTime,@startDt)<=0 and DATEDIFF(N,CreateTime,@endDt) >=0
			select @GuaHaoShuTot = COUNT(*) from ApptOrder where OrderType=0 and OrderStatus=21 and					DATEDIFF (N,CreateTime,@endDt) >=0
		end
		insert into #linshibiao values('GuaHaoShu',@GuaHaoShu,@GuaHaoShuTot,3)
end
if CHARINDEX ( ',4,' , @type_old)>0 or @type=''
begin
	if @startDt='' and @endDt = ''
		begin
			select @JiaoFeiShu=count(*) from BillOrder where orderstatus=8 and paystate=1
			select @JiaoFeiShuTot=0
		end
	if @startDt<>'' and @endDt <> ''
		begin
			select @JiaoFeiShu=count(*) from BillOrder where orderstatus=8 and paystate=1 and DATEDIFF				(N,CreateTime,@startDt)<=0 and DATEDIFF(N,CreateTime,@endDt) >=0
			select @JiaoFeiShuTot=count(*) from BillOrder where orderstatus=8 and paystate=1 and DATEDIFF			(N,CreateTime,@endDt) >=0
		end
		insert into #linshibiao values('JiaoFeiShu',@JiaoFeiShu,@JiaoFeiShuTot,4)
end
if CHARINDEX ( ',5,' , @type_old)>0 or @type=''
begin 
	if @startDt='' and @endDt = ''
		begin
			select @BangKaShu= COUNT(*) from HospCard where DeleteStatus =0
			select @BangKaShuTot=0
		end
	if @startDt<>'' and @endDt <> ''
		begin
			select @BangKaShu= COUNT(*) from HospCard where DeleteStatus =0 and DATEDIFF(N,AddDt,@startDt)<=0 and DATEDIFF(N,AddDt,@endDt) >=0
			select @BangKaShuTot= COUNT(*) from HospCard where DATEDIFF(N,AddDt,@endDt) >=0
			
		end
		insert into #linshibiao values('BangKaShu',@BangKaShu,@BangKaShuTot,5)
end

if CHARINDEX ( ',6,' , @type_old)>0 or @type='' 
begin  
	if @startDt<>'' and @endDt <> ''
		begin
			select @YongHuShu = COUNT(*) from [User] where DATEDIFF(N,SubscribeDt,@startDt)<=0 and DATEDIFF(N,SubscribeDt,@endDt) >=0
			select @YongHuShuTot = COUNT(*) from [User] where DATEDIFF(N,SubscribeDt,@endDt) >=0
			
		end
	if @startDt='' and @endDt = ''
		begin
			select @YongHuShu = COUNT(*) from [User]
			select @YongHuShuTot=0
		end
		insert into #linshibiao values('YongHuShu',@YongHuShu,@YongHuShuTot,6)
end
if CHARINDEX ( ',7,' , @type_old)>0 or @type='' 
begin  
	if @startDt<>'' and @endDt <> ''
		begin
			
			select @YuYueJiaoYiJinE = isnull(SUM(SumFee),0) from ApptOrder where OrderStatus=21 and	PayState=1 and DATEDIFF(N,CreateTime,@startDt)<=0 and DATEDIFF(N,CreateTime,@endDt) >=0
			select @JiaoFeiJiaoYiJinE = isnull(SUM(PrescMoney),0) from BillOrder where OrderStatus=8 and PayState=1 and DATEDIFF(N,CreateTime,@startDt)<=0 and DATEDIFF(N,CreateTime,@endDt) >=0
			select @JiaoYiJinE=@YuYueJiaoYiJinE+@JiaoFeiJiaoYiJinE
			
			select @YuYueJiaoYiJinETot = isnull(SUM(SumFee),0) from ApptOrder where OrderStatus=21 and	PayState=1 and DATEDIFF(N,CreateTime,@endDt) >=0
			select @JiaoFeiJiaoYiJinETot=isnull(SUM(PrescMoney),0) from BillOrder where OrderStatus=8 and	PayState=1 and DATEDIFF(N,CreateTime,@endDt) >=0
			select @JiaoYiJinETot = @YuYueJiaoYiJinETot+@JiaoFeiJiaoYiJinETot
		end
	if @startDt='' and @endDt = ''
		begin
			select @YuYueJiaoYiJinE = isnull(SUM(SumFee),0) from ApptOrder where OrderStatus=21 and	PayState=1
			select @JiaoFeiJiaoYiJinE = isnull(SUM(PrescMoney),0) from BillOrder where OrderStatus=8 and PayState=1
			select @JiaoYiJinE=@YuYueJiaoYiJinE+@JiaoFeiJiaoYiJinE
			select @JiaoYiJinETot=0
		end
		insert into #linshibiao values('JiaoYiJinE',@JiaoYiJinE,@JiaoYiJinETot,7)
end
if CHARINDEX ( ',8,' , @type_old)>0 or @type='' 
begin  
	if @startDt<>'' and @endDt <> ''
		begin
			
			select @YuYueJiaoYiShu = COUNT(*) from ApptOrder where OrderStatus=21 and PayState=1 and					DATEDIFF(N,CreateTime,@startDt)<=0 and DATEDIFF(N,CreateTime,@endDt) >=0
			select @JiaoFeiJiaoYiShu = 0
			select @JiaoYiShu=@YuYueJiaoYiShu+@JiaoFeiJiaoYiShu
			
			select @YuYueJiaoYiShuTot = COUNT(*) from ApptOrder where OrderStatus=21 and PayState=1 and				DATEDIFF(N,CreateTime,@endDt) >=0
			select @JiaoFeiJiaoYiShuTot=0
			select @JiaoYiShuTot = @YuYueJiaoYiShuTot+@JiaoFeiJiaoYiShuTot
		end
	if @startDt='' and @endDt = ''
		begin
			select @YuYueJiaoYiShu = COUNT(*) from ApptOrder where OrderStatus=21 and PayState=1
			select @JiaoFeiJiaoYiShu = 0
			select @JiaoYiShu=@YuYueJiaoYiShu+@JiaoFeiJiaoYiShu
			
			select @JiaoYiShuTot=0
		end
		insert into #linshibiao values('JiaoYiShu',@JiaoYiShu,@JiaoYiShuTot,8)
end
if CHARINDEX ( ',9,' , @type_old)>0 or @type='' 
begin  
	declare @aa int,@bb int,@aaTot int,@bbTot int
	if @startDt<>'' and @endDt <> ''
		begin
			
			select @YuYueJiaoFeiShu =1
			select @YuYueJiaoFeiShuTot =1
		end
	if @startDt='' and @endDt = ''
		begin
			
			select @YuYueJiaoFeiShu =1
			select @YuYueJiaoFeiShuTot=0
		end
		insert into #linshibiao values('YuYueJiaoFeiShu',@YuYueJiaoFeiShu,@YuYueJiaoFeiShuTot,9)
end
if CHARINDEX ( ',10,' , @type_old)>0 or @type='' 
begin  
	if @startDt<>'' and @endDt <> ''
		begin
			select @RiYongHuShu = count(distinct(OpenId)) from EventLog where DATEDIFF(N,AddTime,@startDt)<=0 and DATEDIFF(N,AddTime,@endDt) >=0
			select @RiYongHuShuTot = count(distinct(OpenId)) from EventLog where DATEDIFF(N,AddTime,@endDt) >=0
			
		end
	if @startDt='' and @endDt = ''
		begin
			select @RiYongHuShu = count(distinct(OpenId)) from EventLog
			select @RiYongHuShuTot=0
		end
		insert into #linshibiao values('RiYongHuShu',@RiYongHuShu,@RiYongHuShuTot,10)
end
if CHARINDEX ( ',11,' , @type_old)>0 or @type='' 
begin  
	if @startDt<>'' and @endDt <> ''
		begin
			select @YiShengShu = COUNT(*) from Doctor where DATEDIFF(N,RecCreateDt,@startDt)<=0 and DATEDIFF(N,RecCreateDt,@endDt) >=0
			select @YiShengShuTot = COUNT(*) from Doctor where DATEDIFF(N,RecCreateDt,@endDt) >=0
		end
	if @startDt='' and @endDt = ''
		begin
			select @YiShengShu = COUNT(*) from Doctor
			select @YiShengShuTot=0
		end
		insert into #linshibiao values('YiShengShu',@YiShengShu,@YiShengShuTot,11)
end
select * from #linshibiao
drop table #linshibiao
